create database NAIL_MANAGER
go

use NAIL_MANAGER
go

create table CATALOGS
(
	CatalogsID int identity,
	CatalogsName varchar(50),
	
	primary key(CatalogsID)
)
go

create table ACCOUNT
(
	UserID varchar(20),
	HashPassword varchar(32),
	FirstName varchar(30),
	LastName varchar(30),
	DateOfBirth date,
	UserAddress text,
	PhoneNumber varchar(15),
	Email varchar(50),
	UserPermission int,
	primary key (UserID)
)
go

create table CUSTOMER
(
	CusID int identity,
	FirstName varchar(30),
	LastName varchar(30),
	DateOfBirth date,
	CusAddress text,
	PhoneNumber varchar(15),
	Email varchar(50),
	CreateDate datetime,
	primary key(CusID)
)
go

create table NAIL_SERVICE
(
	ServiceID int identity,
	ServiceName varchar(50),
	ServiceDescription text,
	Price float,
	CatalogsID int,
	primary key(ServiceID),
	foreign key(CatalogsID) references CATALOGS(CatalogsID)
)
go

create table PROMOTION
(
	PromotionID int identity,
	Title varchar(50),
	StartDate datetime,
	EndDate datetime,
	DiscountPercent float,
	primary key(PromotionID)
)
go

create table GIFTCARD_TYPE(
	TypeID int identity,
	Name varchar(30),
	Price float,
	MinTotal float,	
	primary key (TypeID)
)
go

create table TICKET
(
	TicketID int identity,
	UserID varchar(20),
	CusID int,
	PromotionID int,
	GiftCardType int,
	CreateDate datetime,
	Gratuity float,
	Total float,
	primary key(TicketID),
	foreign key (UserID) references ACCOUNT(UserID),
	foreign key (PromotionID) references PROMOTION(PromotionID),
	foreign key (CusID) references CUSTOMER(CusID),
	foreign key (GiftCardType) references GIFTCARD_TYPE(TypeID)
)

create table TICKET_DETAIL(
	TicketID int,
	ServiceID int,
	Quantity int,
	primary key(TicketID,ServiceID),
	foreign key (ServiceID) references NAIL_SERVICE(ServiceID),
)



create table TICKET_GIFTCARD(
	TicketID int identity,
	UserID varchar(20),
	CusID int,
	CreateDate datetime,
	PromotionID int,
	
	primary key (TicketID),
	foreign key (UserID) references ACCOUNT(UserID),
	foreign key (PromotionID) references PROMOTION(PromotionID),
	foreign key (CusID) references CUSTOMER(CusID)
)

create table TICKET_GIFTCARD_DETAIL
(
	TicketID int,
	TypeID int,
	Quantity int,
	primary key (TicketID,TypeID),
	foreign key (TicketID) references TICKET_GIFTCARD(TicketID),
	foreign key (TypeID) references GIFTCARD_TYPE(TypeID),
)